/**
 * Company : Siangklom.
 * Created By : Chatree Siangklom
 * Created On : Aug 22, 2013  4:21:21 PM 
 * e-Mail : dr_chatree@hotmail.com
 * Mobile Phone : (+66) 087-921-5115
 * Package Name : com.tis.database.dao.impl
 * File Name : SdCustomerOutb1DaoImpl.java
 * TODO
 */
package com.tis.database.dao.staging.impl;

import java.math.BigDecimal;
import java.util.ArrayList;
import java.util.Collections;
import java.util.List;
import org.hibernate.HibernateException;
import org.hibernate.Query;
import org.hibernate.Session;
import org.hibernate.criterion.MatchMode;
import org.hibernate.criterion.Restrictions;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.stereotype.Repository;
import promis.utility.CommonService;
import promis.utility.Utilitys;
import com.tis.database.dao.impl.AbstractDaoImpl;
import com.tis.database.dao.staging.SdCustomerOutb1Dao;
import com.tis.database.entitys.SapMessage;
import com.tis.database.entitys.staging.SdCustomerOutb1;

/**
 * @author Administrator
 *
 */
@SuppressWarnings("unchecked")
@Repository
public class SdCustomerOutb1DaoImpl extends AbstractDaoImpl<SdCustomerOutb1, String> implements SdCustomerOutb1Dao {
	
	private static final Logger logging = LoggerFactory.getLogger(SdCustomerOutb1DaoImpl.class);

	protected SdCustomerOutb1DaoImpl() {
		super(SdCustomerOutb1.class);
	}
 
	@Override	
	public List<SdCustomerOutb1> findCustomerByCode(String code) throws Exception, HibernateException {
		return findByCriteria(Restrictions.like("customer", code, MatchMode.ANYWHERE));
	}
 
	/**
	 * (Javadoc)
	 * @see com.tis.database.dao.staging.SdCustomerOutb1Dao#findCustomerByInstanceNo(java.lang.String)
	 * Date =Nov 1, 2013 : 10:03:47 AM 
	 * CreateBy=Chatree Siangklom
	 * TODO
	 */
	@Override
	public List<SdCustomerOutb1> findCustomerByInstanceNo(String instanceNo) throws Exception, HibernateException {
		try {
			
			Session session = getCurrentSession();
			Query q = session.getNamedQuery(SdCustomerOutb1.findByInstanceNo);
			
			if(!CommonService.IsNull(instanceNo))
				q.setParameter("instanceNo", CommonService.IsNullRString(instanceNo).toUpperCase());
			
			List lstt = q.list();
			logging.info("Size=" + CommonService.getSize(lstt));
			List<SdCustomerOutb1> lst = Collections.checkedList(lstt, SdCustomerOutb1.class);
			if(CommonService.getSize(lst) > 0 )
				return lst;
			
		}catch(Exception e){
			e.printStackTrace();
			logging.error(e.getMessage());
			throw e;
		}
		return null;
	}
	
	@Override
	public List<SdCustomerOutb1> findByName(String name) throws Exception, HibernateException {
		try {
			
			//name = "บริษัท รวย เฮง เฮง จำกัด  บริษัท รวย เฮง เฮง จำกัด";
			
			Session session = getCurrentSession();
			Query q = session.getNamedQuery(SdCustomerOutb1.findByName);
				
			q.setString(0, "%"+ CommonService.IsNullRString(name) +"%");
			
			List<SdCustomerOutb1> lst = Collections.checkedList(q.list(), SdCustomerOutb1.class);
			if(CommonService.getSize(lst) > 0 )
				return lst;
			
		}catch(Exception e){
			e.printStackTrace();
		}
		return null;
	}

	/* (non-Javadoc)
	 * @see com.tis.database.dao.SdCustomerOutb1Dao#RefreshObject(java.util.List)
	 */
	@Override	
	public List<SdCustomerOutb1> RefreshObject(List<SdCustomerOutb1> lst) throws HibernateException, Exception {
		try {
			
			Session session = getCurrentSession();
			
			String strValue = "";
			int iRow = CommonService.getSize(lst); 
			for(int i = 0; i < iRow; i++) {
				if(i > 0)
					strValue += "," + Utilitys.strSql(CommonService.IsNullRString(lst.get(i).getId()));
				else //First loop
					strValue = Utilitys.strSql(CommonService.IsNullRString(lst.get(i).getId()));			
			}
			
			Query query = session.getNamedQuery(SdCustomerOutb1.findInID).setString("Id", strValue).setString("p_orderby", "ACS");
			
			if(CommonService.getSize(query.list()) > 0 )			
				return Collections.checkedList(query.list(), SdCustomerOutb1.class);
			else 
				return null;
			
		} catch (HibernateException e) {
			throw e;
		}
	}
	
	/**
	 * (Javadoc)
	 * @see com.tis.database.dao.impl.AbstractDaoImpl#saveOrUpdate(java.util.List)
	 * Date =Oct 3, 2013 : 6:42:42 PM 
	 * CreateBy=Chatree Siangklom
	 * TODO
	 */
	@Override
	public void saveOrUpdate(List<SdCustomerOutb1> e) throws HibernateException {
		super.saveOrUpdate(e);
	}
	
	
	/**
	 * (Javadoc)
	 * @see com.tis.database.dao.impl.AbstractDaoImpl#saveOrUpdate(java.lang.Object)
	 * Date =Oct 3, 2013 : 6:42:45 PM 
	 * CreateBy=Chatree Siangklom
	 * TODO
	 */
	@Override
	public void saveOrUpdate(SdCustomerOutb1 e) throws HibernateException {
		super.saveOrUpdate(e);
	}

	/**
	 * (Javadoc)
	 * @see com.tis.database.dao.impl.AbstractDaoImpl#delete(java.util.List)
	 * Date =Oct 3, 2013 : 6:42:49 PM 
	 * CreateBy=Chatree Siangklom
	 * TODO
	 */
	@Override
	public void delete(List<SdCustomerOutb1> e) throws HibernateException {
		super.delete(e);
	}
	
	
	/**
	 * (Javadoc)
	 * @see com.tis.database.dao.impl.AbstractDaoImpl#delete(java.lang.Object)
	 * Date =Oct 3, 2013 : 6:42:51 PM 
	 * CreateBy=Chatree Siangklom
	 * TODO
	 */
	@Override
	public void delete(SdCustomerOutb1 e) throws HibernateException {
		super.delete(e);
	}
	

	/** (Javadoc)
	 * @see com.tis.database.dao.staging.SdCustomerOutb1Dao#Search(java.lang.String, java.lang.String, java.lang.String, java.lang.String, java.lang.String, java.lang.String, java.lang.String, java.lang.String)
	 * Date =Sep 18, 2013 : 4:50:33 PM 
	 * CreateBy=Chatree Siangklom
	 * TODO
	 */
	@Override
	public List<SdCustomerOutb1> Search(String stagingId,										
										String customerCode,
										String name,
										String idNo,
										String taxId, 
										String instanceNo,
										String syncCreatedOnStart,
										String syncCreatedOnEnd,
										int maxResult) throws HibernateException, Exception {
		
		try {
			
			Session session = getCurrentSession();
			Query q = session.getNamedQuery(SdCustomerOutb1.findBySearch);
			String query = q.getQueryString();
			
			if(!CommonService.IsNull(customerCode))
				query += " And upper(c.customer) like '%"+ CommonService.IsNullRString(customerCode).toUpperCase() +"%'";
			
			if(!CommonService.IsNull(idNo))
				query += " And upper(c.taxcode1) like '%"+ CommonService.IsNullRString(idNo).toUpperCase() +"%' ";			
			
			if(!CommonService.IsNull(taxId))
				query += " And upper(c.taxcode3) like '%"+ CommonService.IsNullRString(taxId).toUpperCase() +"%'";			

			if(!CommonService.IsNull(instanceNo))
				query += " And c.instanceNo like '%"+ CommonService.IsNullRString(instanceNo).toUpperCase() +"%'";
			
			if(!CommonService.IsNull(syncCreatedOnStart) && !CommonService.IsNull(syncCreatedOnEnd))
				query += " And to_char(c.syncCreatedon, 'YYYY-MM-DD') BETWEEN :syncCreatedOnStart And :syncCreatedOnEnd ";
			
			if(!CommonService.IsNull(stagingId))
				query += " And c.pkId like '%"+ CommonService.IsNullRString(stagingId).toUpperCase() +"%'";
			
			if(!CommonService.IsNull(name))
				query += " And c.name1 like :name ";
			
			query += " Order by :p_orderby "; 	
			
			logging.info(query);
			
			q = session.createQuery(query);
			
			if(!CommonService.IsNull(name))
				q.setParameter("name", "%"+ name +"%");
				
			if(!CommonService.IsNull(syncCreatedOnStart))
				q.setParameter("syncCreatedOnStart", CommonService.IsNullRString(syncCreatedOnStart).toUpperCase());
			
			if(!CommonService.IsNull(syncCreatedOnEnd))
				q.setParameter("syncCreatedOnEnd", CommonService.IsNullRString(syncCreatedOnEnd).toUpperCase());
			
			q.setParameter("p_orderby", "ASC");
			
			if(maxResult > 0)
				q.setMaxResults(maxResult);			
			
			List<SdCustomerOutb1> lst = Collections.checkedList(q.list(), SdCustomerOutb1.class);
			if(CommonService.getSize(lst) > 0 )
				return lst;
			else 
				return null;
			
		}catch(HibernateException ex) {
			logging.error(ex.getMessage());
			ex.printStackTrace();
			throw ex;
		}
		catch(Exception ex1) {
			logging.error(ex1.getMessage());
			ex1.printStackTrace();
			throw ex1;
		}
	}

	/** (Javadoc)
	 * @see com.tis.database.dao.staging.SdCustomerOutb1Dao#NextSequence()
	 * Date =Oct 16, 2013 : 10:30:37 AM 
	 * CreateBy=Chatree Siangklom
	 * TODO
	 */
	@Override
	public BigDecimal NextSequence() throws HibernateException, Exception {
		return super.NextSequence("CUSTOMER_OUTB");
	}

	/** (Javadoc)
	 * @see com.tis.database.dao.staging.SdCustomerOutb1Dao#NameSplitToSAP(java.lang.String)
	 * Date =Oct 28, 2013 : 11:38:12 AM 
	 * CreateBy=Chatree Siangklom
	 * TODO
	 * default String name1 = "";
	 * default String name2 = "";
	 * default String name3 = "";
	 * default String name4 = "";
	 */
	@Override
	public List<String> NameSplitToSAP(String name) throws Exception, HibernateException {
		List<String> lst = new ArrayList<>();
		try{
			String name1 = "";
			String name2 = "";
			String name3 = "";
			String name4 = "";
			
			if(!CommonService.IsNull(name)){
				String strTemp = name;
				int length = strTemp.length();
				System.out.println("length=" + length);
				
				if(length <= 35){
					name1 = (String) strTemp.subSequence(0, length);
				}
				else if(length <= 70)
				{
					name1 = (String) strTemp.subSequence(0, 35);
					name2 = (String) strTemp.subSequence(35, length);
				}
				else if(length <= 105)
				{
					name1 = (String) strTemp.subSequence(0, 35);
					name2 = (String) strTemp.subSequence(35, 70);
					name3 = (String) strTemp.subSequence(70, length);				
				}
				else if(length <= 140) {
					name1 = (String) strTemp.subSequence(0, 35);
					name2 = (String) strTemp.subSequence(35, 70);
					name3 = (String) strTemp.subSequence(70, 105);
					name4 = (String) strTemp.subSequence(105, length);
				}else{
					name1 = (String) strTemp.subSequence(0, 35);
					name2 = (String) strTemp.subSequence(35, 70);
					name3 = (String) strTemp.subSequence(70, 105);
					name4 = (String) strTemp.subSequence(105, 140);
				}
				
			}
			
			logging.info("ori name=" + name);
			logging.info("name1 length="+ name1.length() +" (0, 35)=" + name1);
			logging.info("name2  length"+ name2.length() +" (35, 70)=" + name2);
			logging.info("name3 length"+ name3.length() +" (70, 105)=" + name3);
			logging.info("name4 length"+ name4.length() +" (105, 140)=" + name4);
			
			lst.add(name1);
			lst.add(name2);
			lst.add(name3);
			lst.add(name4);
			
		}catch(Exception e){
			e.printStackTrace();
			logging.error(e.getMessage());
		}
		
		return lst;
	}
	
	@Override
	public List<SdCustomerOutb1> UpdateBackSapMessage(List<SapMessage> sapMessages) throws HibernateException, Exception {

			List<SdCustomerOutb1> lst = new ArrayList<SdCustomerOutb1>();
			try{
				if(CommonService.getSize(sapMessages) > 0){
					
					for(SapMessage value : sapMessages){
						SdCustomerOutb1 obj = findById(value.getId());
						if(obj != null){
							
							obj.setSapStagingId(value.getSapStagingId());
							obj.setMsgCode(value.getMsgCode());
							obj.setMsgDesc(value.getMsgDesc());
							obj.setExtRefKey(value.getExtRefKey());
							obj.setExtRefNo(value.getExtRefNo());																					
							obj.setMsgAltDesc(value.getMsgAltDesc());
							obj.setSyncIssync(value.getSyncIssync());
							obj.setSyncUpdatedby(value.getSyncUpdatedby());
							obj.setSyncUpdatedon(value.getSyncUpdatedon());
							lst.add(obj);
						}
					}
					
					if(CommonService.getSize(lst) > 0) saveOrUpdate(lst); else lst = null;
				}
			}catch(Exception e){
				lst = null;
				e.printStackTrace();
				throw e;
			}
			return lst;
	}
}
